﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace OrchardSystemServer.Database
{
    //参考http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL
    public class MySQLHelper
    {
        private MySqlConnection connection;
        public static string connectionString = "Server=" + "localhost" + ";" + "Database=" +
            "orchard_system" + ";" + "Uid=" + "root" + ";" + "Password=" + "root" + ";";
        public bool IsError { get; set; }
        public string LastErrorMessage { get; set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        public MySQLHelper()
        {
            Initialize();
        }
        /// <summary>
        /// 设置连接字符串
        /// </summary>
        /// <param name="str"></param>
        public static void SetConnectString(string str)
        { 
            connectionString=str;
        }

        private void Initialize()
        {
            connection = new MySqlConnection(connectionString);
            IsError = false;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        private bool OpenConnection()
        {
            IsError = false;//每次打开连接前置错误为false
            try
            {
                if (connection.State != ConnectionState.Open)

                    connection.Open();
            }
            catch (MySqlException ex)
            {
                IsError = true;
                switch (ex.Number)
                {
                    case 0:
                        LastErrorMessage = "Can not connect to mysql server,contact administrator.";
                        break;
                    case 1:
                        LastErrorMessage = "Invalid database connection username/password,please try again.";
                        break;
                    default:
                        LastErrorMessage = ex.Message;
                        break;
                }
                return false;
            }
            catch (Exception ex)
            {
                LastErrorMessage = ex.Message;
                IsError = true;
                return false;
            }
            return true;
        }


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        private bool CloseConnection()
        {
            try
            {
                connection.Clone();
            }
            catch (Exception ex)
            {
                LastErrorMessage = ex.Message;
                IsError = true;
                return false;
            }
            return true;
        }

        /// <summary>
        /// 插入
        /// </summary>
        public int Insert(string sql)
        {
            int rows = -1;

            try
            {
                if (this.OpenConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    rows = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                IsError = true;
                LastErrorMessage = ex.Message;
            }
            finally 
            {
                this.CloseConnection();
            }
            return rows;
        }

        /// <summary>
        /// 更新
        /// </summary>
        public int Update(string sql)
        {
            return Insert(sql);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <returns>受影响的行数</returns>
        public int Delete(string sql)
        {
            return Insert(sql);
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns>返回查到的数据</returns>
        public MySqlDataReader Select(string sql)
        {
            MySqlDataReader dataReader = null;
            if (this.OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                dataReader = cmd.ExecuteReader();
                this.CloseConnection();
            }
            return dataReader;
        }

        /// <summary>
        /// 查询记录条数，如select count(*)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回记录的条数</returns>
        public int Count(string sql)
        {
            int count = -1;
            if (this.OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                count = int.Parse(cmd.ExecuteScalar() + "");
                this.CloseConnection();
            }
            return count;

        }
    }
}
